/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package EntityDB;

import entity.User;
import java.lang.invoke.CallSite;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author NgocNam
 */
public class useDB {
    private String URL = "jdbc:sqlserver://localhost;databaseName=LeaveSystemManager";
    private String Use = "sa"; 
    private String Pass = "123456";
    private Connection conn;
        public useDB(){
        try {
            conn=DriverManager.getConnection(URL,Use,Pass);
        } catch (SQLException ex) {
            Logger.getLogger(useDB.class.getName()).log(Level.SEVERE, null, ex);
        }
        }
        
        public boolean addUser(User u){
        try {
            CallableStatement csmt=conn.prepareCall("{call addUser(?,?,?,?,?)}");
            csmt.setString(1, u.getUserName());
            csmt.setString(2, u.getPassWord());
            csmt.setInt(3, u.getAuthorID());
            csmt.setString(4, u.getFullName());
            csmt.getBoolean(5);
            csmt.executeUpdate();
            return true;
        } catch (SQLException ex) {
            Logger.getLogger(useDB.class.getName()).log(Level.SEVERE, null, ex);
        }
            return false;
        }
        
        public boolean updateUser(User u){
        try {
            CallableStatement csmt=conn.prepareCall("{call updateUser}");
            csmt.setString(1, u.getUserName());
            csmt.setString(2, u.getPassWord());
            csmt.setInt(3, u.getAuthorID());
            csmt.setString(4, u.getFullName());
            csmt.setBoolean(5, u.isStatus());
            int row =csmt.executeUpdate();
            if(row>0){
                return true;}
        } catch (SQLException ex) {
            Logger.getLogger(useDB.class.getName()).log(Level.SEVERE, null, ex);
        }
             return false;
        }
        
        
        public boolean removeUser(String userName){
        try {
            CallableStatement csmt=conn.prepareCall("{call removeUser(?)}");
            csmt.setString(1, userName);
            int row=csmt.executeUpdate();
            if(row>0){
                return true;
            }
        } catch (SQLException ex) {
            Logger.getLogger(useDB.class.getName()).log(Level.SEVERE, null, ex);
        }
            return false;
        }
            
        public ArrayList<User> getUser(){
            ArrayList<User> list=new ArrayList<User>();
        try {
            CallableStatement csmt=conn.prepareCall("{call getUser()}");
            ResultSet rs=csmt.executeQuery();
            while(rs.next()){
                String userName=rs.getString(1);
                String passWord=rs.getString(2);
                int authorID=rs.getInt(3);
                String fullName=rs.getString(4);
                boolean gender=rs.getBoolean(5);
                boolean status=rs.getBoolean(6);
                    User u=new User(userName, passWord, authorID, fullName, gender, status);
                    list.add(u);
            }
        } catch (SQLException ex) {
            Logger.getLogger(useDB.class.getName()).log(Level.SEVERE, null, ex);
        }
            return list;
        }
}
